﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using DAL;

/// <summary>
/// 执行多行和单行添加的业务处理
/// </summary>
public class RowsEdit
{
    public RowsEdit(){}

    /// <summary>
    /// 获取DataSet数据集
    /// </summary>
    /// <param name="SQLString">sql查询语句</param>
    /// <returns>DataSet数据集对象</returns>
    public static DataSet GetDataSet(string SQLString)
    {
        DataSet ds = new DataSet();
        ds = DbHelperSql.GetDataSet(SQLString);
        return ds;
    }


    /// <summary>
    /// 数据提交处理
    /// </summary>
    /// <param name="ds">需要更新的数据集</param>
    /// <param name="SQLString">SQL查询语句</param>
    /// <returns>更新结果，返回影响行数</returns>
    public static int DataSubmit(DataSet ds, string SQLString)
    {
        int result = -1;

        //数据库连接字符串(web.config来配置)
        string connectionString = ConfigurationManager.ConnectionStrings["dataBaseConnection"].ConnectionString;
        SqlConnection conn = new SqlConnection(connectionString);
        try
        {
            SqlCommand objComm = new SqlCommand(SQLString, conn);
            SqlDataAdapter da = new SqlDataAdapter(objComm);
            SqlCommandBuilder updataBulid = new SqlCommandBuilder(da);
            result = da.Update(ds.Tables[0]);
            ds.AcceptChanges();
        }
        catch (Exception ex)
        {
            Console.WriteLine("======" + ex.Message);
        }
        return result;
    }

    /// <summary>
    /// 运用事务，基与主细表数据增/删/改处理
    /// </summary>
    /// <param name="sqlMainTable">主表的sql语句</param>
    /// <param name="ds">细表的数据集</param>
    /// <param name="sqlChild">细表的SQL查询语句</param>
    /// <returns>更新结果，返回：1成功，0失败</returns>
    public static int DataSubmitByTransaction(string sqlMainTable, DataSet ds, string sqlChild)
    {
        int result = 0;

        //数据库连接字符串(web.config来配置)
        string connectionString = ConfigurationManager.ConnectionStrings["dataBaseConnection"].ConnectionString;
        SqlConnection conn = new SqlConnection(connectionString);
        conn.Open();
        SqlTransaction trans = conn.BeginTransaction();
        try
        {
            SqlCommand cmdMainTable = new SqlCommand(sqlMainTable, conn);
            cmdMainTable.Transaction = trans;
            cmdMainTable.ExecuteNonQuery();

            SqlCommand objComm = new SqlCommand(sqlChild, conn);

            SqlDataAdapter da = new SqlDataAdapter(objComm);

            da.SelectCommand.Transaction = trans;

            SqlCommandBuilder updataBulid = new SqlCommandBuilder(da);
            result = da.Update(ds.Tables[0]);

            trans.Commit();
            ds.AcceptChanges();
            result = 1;
        }
        catch (Exception ex)
        {
            string mesg = ex.Message;

            trans.Rollback();

            //
        }
        finally
        {
            if (conn != null)
                conn.Close();
        }
        return result;
    }


    /// <summary>
    /// 临时表删除操作
    /// </summary>
    /// <param name="ds">临时数据集</param>
    /// <param name="dsView">视图数据集</param>
    /// <param name="columnName">主键PKID列名</param>
    /// <param name="id">pkid值</param>
    /// <returns>删除的子表主键</returns>
    public static string RowDelete(ref DataSet ds,ref DataSet dsView, string columnName, string pkid,string childPkidName)
    {
        string childPkid = String.Empty;
        foreach (DataRow dr in dsView.Tables[0].Rows)
        {
            if (dr.RowState != DataRowState.Deleted)
            {
                if (dr[columnName].ToString() == pkid)
                {
                    childPkid = dr[childPkidName].ToString();
                    int i = dsView.Tables[0].Rows.IndexOf(dr);
                    dsView.Tables[0].Rows[i].Delete();
                    ds.Tables[0].Rows[i].Delete();
                    break;
                }
            }
        }
        return childPkid;
    }

    /// <summary>
    /// 临时表删除操作
    /// </summary>
    /// <param name="ds">临时数据集</param>
    /// <param name="dsView">视图数据集</param>
    /// <param name="columnName">主键PKID列名</param>
    /// <param name="id">pkid值</param>
    /// <returns>删除的子表主键</returns>
    public static string SingleTypeRowDelete(ref DataSet ds, string columnName, string pkid, string childPkidName)
    {
        string childPkid = String.Empty;
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            if (dr.RowState != DataRowState.Deleted)
            {
                if (dr[columnName].ToString() == pkid)
                {
                    childPkid = dr[childPkidName].ToString();
                    int i = ds.Tables[0].Rows.IndexOf(dr);
                    ds.Tables[0].Rows[i].Delete();
                    break;
                }
            }
        }
        return childPkid;
    }


}
